from flask import Flask, request, session, g, redirect, url_for, abort, \
     render_template, flash
#import oursql
import MySQLdb

import datetime
import time


DEBUG = True
SECRET_KEY = 'development key'

# create our little application :)
app = Flask(__name__)
app.config.from_object(__name__)

def connect_db():
#        return oursql.connect(host='127.0.0.1', user='root', passwd='',
#             db='BridgeIt') 
        return MySQLdb.connect(host='127.0.0.1', user='root', passwd='',
             db='bridgeit')  


@app.before_request
def before_request():
    """Make sure we are connected to the database each request."""
    g.db = connect_db()


@app.after_request
def after_request(response):
    """Closes the database again at the end of the request."""
    g.db.close()
    return response

#@app.route('/', methods=['GET', 'POST'])
#def login():
#    error = None
#    if request.method == 'POST':
#        if request.form['username'] != app.config['USERNAME']:
#            error = 'Invalid username'
#        elif request.form['password'] != app.config['PASSWORD']:
#            error = 'Invalid password'
#        else:
#            session['logged_in'] = True
#            flash('You were logged in')
#            return redirect(url_for('view_profile'))
#    return render_template('login.html', error=error)

def isUser(username,password):
    curs = g.db.cursor()
    curs.execute("SELECT id FROM `User` WHERE username= '"+username+"'AND password='"+password+"';")
    res = curs.fetchone()
    curs.close()
    return res

@app.route('/login', methods=['GET', 'POST'])
def login():
    error = None
    if request.method == 'POST':
        i = isUser(request.form['username'],request.form['password'])
        if i > 0:
            session['logged_in']= True
            session['user_id'] = i[0]
            flash('Logged in successfully.')
            return render_template('Search.html', bridges=getLatestFiveBridges())
        else:
            flash('Invalid Username or Password')
    return render_template('login.html', error=error)

@app.route('/logout')
def logout():
    session.pop('logged_in', None)
    flash('You were logged out')
    return render_template('login.html')


@app.route('/')    
def search():
    #session['logged_in']= False
    return render_template('Search.html', bridges=getLatestFiveBridges())


@app.route('/search_button_pressed' , methods=['GET' , 'POST'])
def search_button_pressed():
    containTextField = request.form['containTextField']
    fromList = request.form['fromList']
    toList = request.form['toList']
    errorFrom = None
    errorTo = None
    if fromList == '':          
        errorFrom = 'missing from Country'
    if toList == '':
        errorTo = 'missing to Country'
    if errorFrom==None and errorTo==None:
            cursor = g.db.cursor()
            cursor.execute("select id from BridgeIt.Country where name='"+fromList+"'")
            from_Country = [(row1[0]) for row1 in cursor.fetchall()]
            ins = from_Country[0]
            cursor.close()
            cursor = g.db.cursor()
            cursor.execute("select id from BridgeIt.Country where name='"+toList+"'")
            to_Country = [(row2[0])for row2 in cursor.fetchall()]
            ins2 = to_Country[0]
            cursor.close()
            cursor = g.db.cursor()
            cursor.execute("select * from BridgeIt.Bridge where from_country_id="+str(ins)+" AND to_country_id="+str(ins2)+" AND text LIKE '%"+containTextField+"%'")
            searchResults = [dict(bridgeid=row3[0],
                             datetime=row3[1], 
                          	 text=row3[2],
                          	 userid=row3[3],
                          	 fromcountry=row3[4],
                          	 tocountry=row3[5],
                          	 photourl=row3[6],
                          	 videourl=row3[7],
                          	 likevideos=row3[8],
                           	 dislikes=row3[9],
                          	 spams=row3[10]) for row3 in cursor.fetchall()]
    	   cursor.close()
    	   if session['logged_in'] == True:
            	w = []
            	i = -1
            	for x in searchResults:
                    	i = i + 1
                    	currentUserId = x['userid']
                    	if currentUserId == session['user_id']:
                            	w.append(1)
                    	elif x['spams'] == 20:
                                  	w.append(3)
                    	else:
                          	cursor = g.db.cursor()  
                         	cursor.execute("SELECT COUNT(*) FROM BridgeIt.`userspambridge` WHERE user_id="+str(session['user_id'])+" AND bridge_id="+str(x['bridgeid'])+";")
                         	c1 = cursor.fetchone()
                          	cursor.close()
                          	cursor = g.db.cursor()
                          	cursor.execute("SELECT COUNT(*) FROM bridgeit.userlikebridge u WHERE user_id="+str(session['user_id'])+" AND bridge_id="+str(x['bridgeid'])+";")
                          	c2 = cursor.fetchone()
                          	cursor.close()
                          	cursor = g.db.cursor()
                          	cursor.execute("SELECT COUNT(*) FROM BridgeIt.`userdislikebridge` WHERE user_id="+str(session['user_id'])+" AND bridge_id="+str(x['bridgeid'])+";")
                          	c3 = cursor.fetchone()
                          	cursor.close()
                          	c11 = c1[0]
                          	c22 = c2[0]
                          	c33 = c3[0]
                          	if c11 + c22 + c33 == 0:
                                  	w.append(2)
                          	elif c11 == 1:
                                  	w.append(3)
                          	elif c22 == 1:
                                  	w.append(4)
                          	else:
                                  	w.append(5)
            return render_template('search_res_user.html',searchResults=searchResults,w=w,l=len(searchResults))
    return render_template('search_button_pressed.html',searchResults=searchResults)





@app.route('/view_profile')
def view_profile():
    cursor = g.db.cursor()
#    cursor.execute('call sp_fetch_users('+str(session['user_id'])+')')
    cursor.execute("""SELECT f_name,l_name,dob,Country.name AS 'Country',gender,occupation,email,User.id,username,password FROM User,Country WHERE User.country_id=Country.id AND User.id="""+str(session['user_id']))
    users = [dict(fname=row[0], 
                  lname=row[1],
                  dob=row[2].date(),
                  country=row[3],
                  gender=row[4],
                  occupation=row[5],
                  email=row[6],
                  userid=row[7]) for row in cursor.fetchall()]
    cursor.close()
    cursor = g.db.cursor()
#    cursor.execute('call sp_fetch_user_bridges('+str(session['user_id'])+')')
    cursor.execute("""SELECT date_time,text,CountryFROM.name,CountryTO.name,photo,video,like_count,dislike_count FROM Bridge,Country AS CountryTO,Country AS CountryFROM WHERE Bridge.user_id="""+str(session['user_id'])+""" AND Bridge.from_country_id = CountryFROM.id AND Bridge.to_country_id = CountryTO.id;""")

    userbridges = [dict(datetime=row2[0], 
                        text=row2[1],
                        fromcountry=row2[2],
                        tocountry=row2[3],
                        photourl=row2[4],
                        videourl=row2[5],
                        likes=row2[6],
                        dislikes=row2[7]) for row2 in cursor.fetchall()]
    cursor.close()
    cursor = g.db.cursor()
#    cursor.execute('call sp_fetch_user_likedbridges('+str(session['user_id'])+')')
    cursor.execute("""SELECT date_time,text,CountryFROM.name,CountryTO.name,photo,video,like_count,dislike_count FROM Bridge,Country AS CountryTO,Country AS CountryFROM, UserLikeBridge WHERE UserLikeBridge.user_id="""+str(session['user_id'])+""" AND UserLikeBridge.bridge_id = Bridge.id AND Bridge.from_country_id = CountryFROM.id AND Bridge.to_country_id = CountryTO.id;
""")
    userLikedbridges = [dict(datetime=row3[0], 
                        text=row3[1],
                        fromcountry=row3[2],
                        tocountry=row3[3],
                        photourl=row3[4],
                        videourl=row3[5],
                        likes=row3[6],
                        dislikes=row3[7]) for row3 in cursor.fetchall()]
#    users = [dict(fname="tamer", lname="sherif")]
    return render_template('view_profile.html', users=users,userLikedbridges=userLikedbridges,userbridges=userbridges)

@app.route('/edit_profile')
def edit_profile():
    cursor = g.db.cursor()
#    cursor.execute('call sp_fetch_users('+str(session['user_id'])+')')
    cursor.execute("""SELECT f_name,l_name,dob,Country.name AS 'Country',gender,occupation,email,User.id,username,password FROM User,Country WHERE User.country_id=Country.id AND User.id="""+str(session['user_id']))
    user = [dict(fname=row[0], 
                  lname=row[1],
                  day=row[2].day,
                  month=row[2].month,
                  year=row[2].year,
                  country=row[3],
                  gender=row[4],
                  occupation=row[5],
                  email=row[6],
                  userid=row[7],
                  username=row[8],
                  password=row[9]) for row in cursor.fetchall()]
    return render_template('edit_profile.html', user=user)

@app.route('/update_profile', methods=['GET', 'POST'])
def update_profile():
    #insert into database
    firstname = str(request.form['firstname'])
    errorFN = None
    lastname= str(request.form['lastname'])
    errorLN = None
    month = str(request.form['month'])
    errorMonth = None
    day = str(request.form['day'])
    errorDay = None
    year = str(request.form['year'])
    errorYear = None
    gender = str(request.form['gender'])
    if gender=='Male':
            ge = 'M'
    elif gender=='Female':
                ge = 'F'    
    errorGender = None
    country = str(request.form['country'])
    errorCountry = None
    email =str(request.form['email'])
    errorEmail = None
    occupation = str(request.form['occupation'])
    username = str(request.form['username'])
    errorUsername = None
    password = str(request.form['password'])
    confirmpassword = str(request.form['confirmpassword'])
    errorPassword = None
    
    if request.method == 'POST':
        if firstname == '':          
            errorFN = 'missing firstname'
        if lastname == '':
            errorLN = 'missing lastname'
        if month == '':
            errorMonth = 'missing month'
        if day =='':
            errorDay = 'missing day'
        if year =='':
            errorYear = 'missing year'
        if gender == '':
            errorGender = 'missing gender'
        if country =='':
            errorCountry ='missing home country'
        if email =='' or '@' not in email:
            errorEmail ='not a valid email'
        if password=='' or confirmpassword =='':
            errorPassword = 'please fill both the password and its confirmation fields'
        if password != confirmpassword:
            errorPassword = 'mismatching passwords'
        if errorFN==None and errorLN==None and errorMonth==None and errorDay==None and errorYear==None and errorGender==None and errorCountry==None and errorEmail==None and errorUsername==None and errorPassword==None:
            date = str(request.form['year'])+'-'+str(request.form['month'])+'-'+str(request.form['day'])     
            cursor = g.db.cursor()
            cursor.execute ('SELECT id FROM `country` WHERE name = \''+country+"\'")
            cti = [(row[0]) for row in cursor.fetchall()]
            ct = cti[0]
            print ct
            cursor.close()
            cursor = g.db.cursor()
            query = 'call update_user(1,\''+firstname+'\',\''+lastname+'\',\''+date+'\','+str(ct)+',\''+ge+'\',\''+occupation+'\',\''+email+'\',\''+password+'\')'
#            cursor.execute('call update_user(1,\''+firstname+'\',\''+lastname+'\',\''+date+'\','+str(ct)+',\''+ge+'\',\''+occupation+'\',\''+email+'\',\''+password+'\')')
#            cursor.execute('call update_user(?,?,?,?,?,?,?,?,?)',(1,firstname,lastname,date,ct,ge,occupation,email,password))
            cursor.execute("""UPDATE User SET f_name='"""+firstname+"""',l_name='"""+lastname+"""',dob='"""+date+"""',country_id='"""+str(ct)+"""',gender='"""+ge+"""',occupation='"""+occupation+"""',email='"""+email+"""',password='"""+password+"""' WHERE id='"""+str(session[user_id])) 
            if occupation !='':
                     curs = g.db.cursor()
                     curs.execute ('Update User set occupation=\''+occupation+'\'where username=\''+username+'\'')
                     curs.close()
             
            flash("Profile Successfully Updated!")
            return redirect (url_for('view_profile'))
        return render_template('update_profile.html', firstname=firstname, errorFN=errorFN,
                           lastname=lastname, errorLN=errorLN, month=month, errorMonth=errorMonth,
                               day=day, errorDay=errorDay, year=year, errorYear=errorYear,
                               gender=gender, errorGender=errorGender, country=country, errorCountry=errorCountry,
                               email=email, errorEmail=errorEmail, username=username, errorUsername=errorUsername,
                               errorPassword=errorPassword)

@app.route('/registration')
def registration():
        return render_template('registration.html')

@app.route('/add', methods=['GET', 'POST'])
def add_entry():
    #insert into database
    firstname = str(request.form['firstname'])
    errorFN = None
    lastname= str(request.form['lastname'])
    errorLN = None
    month = str(request.form['month'])
    errorMonth = None
    day = str(request.form['day'])
    errorDay = None
    year = str(request.form['year'])
    errorYear = None
    gender = str(request.form['gender'])
    if gender=='Male':
            ge = 'M'
    elif gender=='Female':
                ge = 'F'    
    errorGender = None
    country = str(request.form['country'])
    errorCountry = None
    email =str(request.form['email'])
    errorEmail = None
    username = str(request.form['username'])
    errorUsername = None
    password = str(request.form['password'])
    confirmpassword = str(request.form['confirmpassword'])
    errorPassword = None

    occupation = str(request.form['occupation'])
    question1 = str(request.form['question1'])
    question2 = str(request.form['question2'])
    
    if request.method == 'POST':
        if firstname == '':          
            errorFN = 'missing firstname'
        if lastname == '':
            errorLN = 'missing lastname'
        if month == '':
            errorMonth = 'missing month'
        if day =='':
            errorDay = 'missing day'
        if year =='':
            errorYear = 'missing year'
        if gender == '':
            errorGender = 'missing gender'
        if country =='':
            errorCountry ='missing home country'
        if email =='' or '@' not in email:
            errorEmail ='not a valid email'
        if username=='':
            errorUsername = 'missing username'
        if username !='':
                 curs = g.db.cursor()
                 curs.execute ('SELECT username FROM `User`')
                 us = [(row[0]) for row in curs.fetchall()]
                 if str(username) in us:
                        errorUsername = 'username already used'    
        if password=='' or confirmpassword =='':
            errorPassword = 'please fill both the password and its confirmation fields'
        if password != confirmpassword:
            errorPassword = 'mismatching passwords'
        if errorFN==None and errorLN==None and errorMonth==None and errorDay==None and errorYear==None and errorGender==None and errorCountry==None and errorEmail==None and errorUsername==None and errorPassword==None:
             date = str(request.form['year'])+'-'+str(request.form['month'])+'-'+str(request.form['day'])
             
             curs = g.db.cursor()
             curs.execute ('SELECT id FROM `Country` WHERE name = \''+country+"\'")
             cti = [(row[0]) for row in curs.fetchall()]
             ct = cti[0]
             print ct
             curs.close()
   
             curs = g.db.cursor()

             curs.execute ('INSERT INTO `User` (f_name, l_name, dob, gender, country_id, email, username, password) VALUES (?, ?, ?, ?, ?, ?, ?, ?)',
                           (firstname,lastname,date,ge,ct,email,username,password))
             curs.close()
             if occupation !='':
                     curs = g.db.cursor()
                     curs.execute ('Update User set occupation=\''+occupation+'\'where username=\''+username+'\'')
                     curs.close()
             if question1 !='':
                     curs = g.db.cursor()
                     curs.execute ('SELECT id FROM `User` WHERE username = \''+username+"\'")
                     IDs = [(row[0]) for row in curs.fetchall()]
                     ID = IDs[0]
                     print ID
                     curs.close()

                     curs = g.db.cursor()
                     curs.execute ('SELECT id FROM `Country` WHERE name = \'All\'')
                     ctall = [(row[0]) for row in curs.fetchall()]
                     ctiall = ctall[0]
                     curs.close()
             
                     now = datetime.datetime.now()
                     curs = g.db.cursor()
                     curs.execute ('INSERT INTO `Bridge` (date_time, text, user_id, from_country_id, to_country_id) VALUES (?, ?, ?, ?, ?)',
                           (now,question1,ID,ct,ctiall))
                     curs.close()
             if question2 !='':
                     curs = g.db.cursor()
                     curs.execute ('SELECT id FROM `User` WHERE username = \''+username+'\'')
                     IDs = [(row[0]) for row in curs.fetchall()]
                     ID = IDs[0]
                     print ID
                     curs.close()

                     curs = g.db.cursor()
                     curs.execute ('SELECT id FROM `Country` WHERE name = \'All\'')
                     ctall = [(row[0]) for row in curs.fetchall()]
                     ctiall = ctall[0]
                     curs.close()
             
                     now = datetime.datetime.now()
                     curs = g.db.cursor()
                     curs.execute ('INSERT INTO `Bridge` (date_time, text, user_id, from_country_id, to_country_id) VALUES (?, ?, ?, ?, ?)',
                           (now,question2,ID,ct,ctiall))
                     curs.close()
             flash ('You have been successfully registered. Please log in.')        
             return redirect (url_for('login'))
      
        return render_template('registration.html', firstname=firstname, errorFN=errorFN,
                           lastname=lastname, errorLN=errorLN, month=month, errorMonth=errorMonth,
                               day=day, errorDay=errorDay, year=year, errorYear=errorYear,
                               gender=gender, errorGender=errorGender, country=country, errorCountry=errorCountry,
                               email=email, errorEmail=errorEmail, username=username, errorUsername=errorUsername,
                               errorPassword=errorPassword, question1=question1, question2=question2)


def likeBridge(BridgeId,UserId):
    conn = oursql.connect(host='127.0.0.1',user='root', passwd='hellothere',db='BridgeIt')
    c = conn.cursor()
    c.execute("SELECT like_count FROM `Bridge` WHERE id = "+str(BridgeId)+";")
    c1 = c.fetchone()[0]
    c.execute("UPDATE `Bridge` SET like_count = ("+str(c1)+" + 1) where id = "+str(BridgeId)+";")
    c.execute("INSERT INTO `UserLikeBridge` (user_id,bridge_id) VALUES("+str(UserId)+","+str(BridgeId)+");")
    c.close()

def dislikeBridge(BridgeId,UserId):
    conn = oursql.connect(host='127.0.0.1',user='root', passwd='hellothere',db='BridgeIt')
    c = conn.cursor()
    c.execute("SELECT dislike_count FROM `Bridge` WHERE id = "+str(BridgeId)+";")
    c1 = c.fetchone()[0]
    c.execute("UPDATE `Bridge` SET dislike_count = ("+str(c1)+" + 1) where id = "+str(BridgeId)+";")
    c.execute("INSERT INTO `UserDislikeBridge` (user_id,bridge_id) VALUES("+str(UserId)+","+str(BridgeId)+");")
    c.close()

def spamBridge(BridgeId,UserId):
    conn = oursql.connect(host='127.0.0.1',user='root', passwd='hellothere',db='BridgeIt')
    c = conn.cursor()
    c.execute("INSERT INTO `UserSpamBridge` (user_id,bridge_id) VALUES("+str(UserId)+","+str(BridgeId)+");")
    c.execute("SELECT spam_count FROM `Bridge` WHERE id = "+str(BridgeId)+";")
    c1 = c.fetchone()[0]
    c.execute("UPDATE `Bridge` SET spam_count = ("+str(c1)+" + 1) where id = "+str(BridgeId)+";")
    c.close()

def getCountryId(countryName):
	cur = g.db.cursor()
	cur.execute('SELECT id FROM Country WHERE name = ?', [countryName])
	country = cur.fetchone()
	return country[0]

def getTags():
	cur = g.db.cursor()
	cur.execute('SELECT id, text FROM Tag')
	tags = [dict(id=row[0], text=row[1]) for row in cur.fetchall()]
	return tags

def getTagId(tagName, tagList):
	for tag in tagList:
		if tagName == tag['text']:
			return tag['id']

def getUsername(id):
	cur = g.db.cursor()
	cur.execute('SELECT username FROM `User` WHERE id = ?', [id])
	user = cur.fetchone()
	return user[0]

@app.route('/like', methods=['POST'])
def like():
	if session.get('logged_in'):
		try:
			likeBridge(request.form['bridge_id'], request.form['user_id'])
		except:
			flash("You already Like this Bridge")
			return redirect(url_for('search'))
		flash("Like Done")
		return redirect(url_for('search'))
	else:
		return redirect(url_for('login'))

@app.route('/dislike', methods=['POST'])
def dislike():
	if session.get('logged_in'):
		try:
			dislikeBridge(request.form['bridge_id'], request.form['user_id'])
		except:
			flash("You already dislike this Bridge")
			return redirect(url_for('search'))
		flash("Dislike Done")
		return redirect(url_for('search'))
	else:
		return redirect(url_for('login'))

@app.route('/spam', methods=['POST'])
def spam():
	if session.get('logged_in'):
		try:
			spamBridge(request.form['bridge_id'], request.form['user_id'])
		except:
			flash("You already spammed this Bridge")
			return redirect(url_for('search'))
		flash("Spam Done")
		return redirect(url_for('search'))
	else:
		return redirect(url_for('login'))

def getLatestFiveBridges():
	cur = g.db.cursor()
	cur.execute('SELECT id, date_time, text, user_id FROM Bridge ORDER BY date_time desc LIMIT 5')
	bridges = [dict(id=row[0], date_time=row[1], text=row[2], username=getUsername(row[3])) for row in cur.fetchall()]
	return bridges

@app.route('/addBridge', methods=['GET', 'POST'])
def addBridge():
	if session.get('logged_in'):
		error = None
		if request.method == 'POST':
			if not request.form['bridge']:
				error = "Bridge field is missing"
			else:
				userId = session['user_id']
				fromCountry = request.form['fromList']
				toCountry = request.form['toList']
				bridge = request.form['bridge']
				tagsList = request.form['tags'].split()
				fromCountryId = getCountryId(fromCountry)
				toCountryId = getCountryId(toCountry)
				
				cur = g.db.cursor()
				cur.execute('INSERT INTO Bridge (date_time, text, user_id, from_country_id, to_country_id) values (?, ?, ?, ?, ?)', \
						[datetime.datetime.now(), bridge, userId, fromCountryId, toCountryId])
				bridgeId = cur.lastrowid
				
				currentTags = getTags()
				
				currentTagList = []
				for tag in currentTags:
					currentTagList.append(tag['text'])

				for tag in tagsList:
					if tag not in currentTagList:
						cur.execute('INSERT INTO Tag (text) values (?)', [tag])
						cur.execute('INSERT INTO BridgeTag (bridge_id, tag_id) values (?, ?)', [bridgeId, cur.lastrowid])
					else:
						cur.execute('INSERT INTO BridgeTag (bridge_id, tag_id) values (?, ?)', [bridgeId, getTagId(tag, currentTags)])
							
				flash("Bridge Created Successfully")
				return redirect(url_for('search'))
		return render_template('addBridge.html', error=error)	
	else:
		return redirect(url_for('login'))

if __name__ == '__main__':
    app.run()

